SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 ----DECLARE @FundId VARCHAR(10) = 'FSIC'
 --DECLARE @RefDate    DATETIME = '1/14/2013'
 --DECLARE @DateOffset INT      = -3
 
CREATE FUNCTION [Live].[GetExportsByNAVDateOffset] (
	@DateOffset INT  ,
	@FundId varchar(10)  = NULL	   
 )
 RETURNS @Results TABLE (
	FundId VARCHAR(10),
	BaseExportId INT,
	OffsetExportId INT,
	BaseNAVDate SMALLDATETIME,
	OffsetNAVDate SMALLDATETIME
	)
 AS BEGIN
    	
    
 ;
 WITH mycte AS
       (
       SELECT    MAX(ExportId) ExportId
           , NAVDate
           , FundId
           , ROW_NUMBER() OVER(PARTITION BY FundId ORDER BY NAVDate)    rowNumber
           , ROW_NUMBER() OVER(PARTITION BY FundId ORDER BY NAVDate) + ABS(@DateOffset) AS lead
           , ROW_NUMBER() OVER(PARTITION BY FundId ORDER BY NAVDate) - ABS(@DateOffset) AS lag
          FROM FSFundModel.Reporting.Export
          GROUP BY FundId
           , NAVDate
       )
    
    INSERT INTO @Results
          
	    SELECT    src.FundId    AS FundId 
		, src.ExportId      AS BaseExportId
		,CASE WHEN @DateOffset > 0 THEN Future.ExportId ELSE Historic.ExportId END AS offsetExportId
		, src.NAVDate		AS BaseNAVDate
		,CASE WHEN @DateOffset > 0 THEN Future.NAVDate ELSE	Historic.NAVDate END AS offsetNAVDate
		
       FROM mycte           AS Src
          LEFT JOIN mycte   AS Historic
             ON Src.rowNumber = Historic.lead
             AND src.FundId   = Historic.FundId
          
          LEFT JOIN mycte AS Future
             ON Src.rowNumber  = Future.lag
             AND Future.FundId = Src.FundId
		WHERE src.FundId = ISNULL(@FundId, src.FundId)

       ORDER BY src.FundId, src.NAVDate ASC
RETURN	
END		
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'Live', 'FUNCTION', N'GetExportsByNAVDateOffset', NULL, NULL
GO
